package com.ckv.qcm.model.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.ckv.qcm.model.Section;
import com.ckv.qcm.model.Test;
import com.ckv.qcm.model.Trainer;

public class TestDAO extends DAO
{
	public ArrayList<Long> findQuestionsBySession(Long idSession) throws SQLException
	{
		ArrayList<Long> ret=new ArrayList<Long>();
		
		ResultSet result;
		try {
			result = this.connection.createStatement().executeQuery(
					"SELECT QUESTIONS.idQuestion FROM QUESTIONS " +
			"INNER JOIN Rel_Question_Section ON Rel_Question_Section.idQuestion=QUESTIONS.idQuestion " +
			"INNER JOIN SECTIONS ON SECTIONS.idSection=Rel_Question_Section.idSection " +
			"INNER JOIN TESTS ON SECTIONS.idTest=TESTS.idTest " +
			"INNER JOIN SESSIONS ON SESSIONS.idTest=TESTS.idTest " + 
			"WHERE SESSIONS.idSession=" + idSession);
		
		
		while(result.next())
		{
			ret.add(result.getLong("idQuestion"));
		}
		
		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			throw e;
			
		}			 
		return ret;
	}
	
	
	public int getNbQuestions(Long idTest) throws SQLException
	{
		int ret=0;
		
		try {
		ResultSet	result=this.connection.createStatement().executeQuery("select dbo.getnbQuestion(" + idTest +") as b");
		
		if (result.next())
		{
			ret=result.getInt("b");
		}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			throw e;
		}
		return ret;
	}

	public ArrayList<Long> getSectionIds(Long idTest) throws SQLException
	{
		ArrayList<Long> ret = new ArrayList<Long>();
		
		try {
			ResultSet result=this.connection.createStatement().executeQuery(
					"SELECT SECTIONS.idSection FROM SECTIONS " +
					"WHERE SECTIONS.idTest=" + idTest);
			
			while(result.next())
			{
				ret.add(result.getLong("idSection"));
			}
			
			
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			throw e;
		}
		return ret;
	}

	
	public Test find(long id)
	{
		Test test = null;
		ArrayList<Section> sections = new ArrayList<Section>();

		try
		{
			ResultSet result = this.connection.createStatement().executeQuery(
					"SELECT * FROM Tests WHERE idTest = " + id);

			if (result.next())
			{
				test = new Test(id, result.getString("Nom"),
						result.getInt("TempsDePassage"),
						result.getFloat("SeuilReussite"));


//				result = this.connection
//						.createStatement()
//						.executeQuery(
//								"SELECT idSection FROM Tests LEFT OUTER JOIN Sections ON Tests.idTest = Sections.idTest WHERE Tests.idTest = "
//										+ id);
//
//				while (result.next())
//					sections.add(Section.find(result.getLong("idSection")));


				result = this.connection
						.createStatement()
						.executeQuery(
								"SELECT Tests.idFormateur FROM Tests LEFT OUTER JOIN Formateurs ON Tests.idFormateur = Formateurs.idFormateur WHERE Tests.idTest = "
										+ id);

				if (result.next())
				{
					test.setTrainer(Trainer.find(result.getLong("idFormateur")));
				}

			}
			test.setSections(sections);
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		return test;
	}

	public ArrayList<Test> findAll()
	{
		ArrayList<Test> tests = new ArrayList<Test>();

		try
		{
			ResultSet result = this.connection.createStatement().executeQuery(
					"SELECT idTest FROM Tests");

			while (result.next())
				tests.add(this.find(result.getLong("idTest")));

		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}

		return tests;
	}

	public Test create(Test test)
	{
		ResultSet generatedKeys = null;
		long idTest = 0;
		try
		{
			PreparedStatement preparedStatement = this.connection
					.prepareStatement(
							"INSERT INTO Tests (Nom, TempsDePassage, Seuil) VALUES (?, ? ,?)",
							Statement.RETURN_GENERATED_KEYS);
			preparedStatement.setString(1, test.getName());
			preparedStatement.setInt(2, test.getTime());
			preparedStatement.setFloat(3, test.getTreshold());

			int affectedRows = preparedStatement.executeUpdate();
			if (affectedRows == 0)
			{
				throw new SQLException(
						"Creating answer failed, no rows affected.");
			}

			generatedKeys = preparedStatement.getGeneratedKeys();
			if (generatedKeys.next())
			{
				idTest = generatedKeys.getLong(1);
			}
			else
			{
				throw new SQLException(
						"Creating answer failed, no generated key obtained.");
			}

			for (Section section : test.getSections())
			{
				section.setIdTest(idTest);
				section.create();
			}

			test = this.find(idTest);

		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		return test;
	}

	public Test update(Test test)
	{
		try
		{
			PreparedStatement preparedStatement = this.connection
					.prepareStatement("UPDATE Questions SET Nom = '"
							+ test.getName() + "', TempsdePassage = '"
							+ test.getTime() + "', Seuil = '"
							+ test.getTreshold() + "'");

			preparedStatement.executeUpdate();

			for (Section section : test.getSections())
			{
				section.update();
			}

			test = this.find(test.getId());

		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		return null;
	}

	public void delete(Test test)
	{
		try
		{

			this.connection.createStatement().executeUpdate(
					"DELETE FROM Tests WHERE idTest = " + test.getId());

			for (Section section : test.getSections())
			{
				section.delete();
			}

		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
	}

}
